﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Reflection;
using InterpriseSuiteEcommerceCommon.DTO;
using InterpriseSuiteEcommerceCommon.Extensions;
using InterpriseSuiteEcommerceCommon.Tool;

namespace InterpriseSuiteEcommerceCommon.DataAccess
{
    public static class LookupDA
    {
        private const string EXTRACODE = "101";

        public static List<CountryAddressDTO> GetAllCountries()
        {
            string methodName = string.Format("{0}_{1}",MethodBase.GetCurrentMethod().Name, EXTRACODE);

            var requestCache = CachingFactory.RequestCachingEngineInstance;
            if (requestCache.Exist(methodName))
            {
                return requestCache.GetItem<List<CountryAddressDTO>>(methodName);
            }

            var countries = new List<CountryAddressDTO>();
            using (var con = DB.NewSqlConnection())
            {
                con.Open();
                using (var reader = DB.GetRSFormat(con, "SELECT sc.CountryCode, sc.IsWithState, sc.IsSearchablePostal, CASE WHEN sci.Country IS NOT NULL THEN 0 ELSE 1 END AS IsHomeCountry FROM SystemCountry sc with (NOLOCK) LEFT OUTER JOIN SystemCompanyInformation sci with (NOLOCK) ON sci.Country = sc.CountryCode WHERE sc.IsActive = 1 AND sc.IsShowOnWeb = 1 ORDER BY IsHomeCountry, sc.CountryCode ASC"))
                {
                    while (reader.Read())
                    {
                        var country = new CountryAddressDTO(DB.RSField(reader, "CountryCode"), DB.RSFieldBool(reader, "IsWithState"), DB.RSFieldBool(reader, "IsSearchablePostal"));
                        countries.Add(country);
                    }
                }
            }

            requestCache.AddItem(methodName, countries);

            return countries;
        }

        public static string GetLanguageCodes(string countryCode)
        {
            string methodName = string.Format("{0}_{1}_{2}", MethodBase.GetCurrentMethod().Name, countryCode, EXTRACODE);

            var requestCache = CachingFactory.RequestCachingEngineInstance;
            if (requestCache.Exist(methodName))
            {
                return requestCache.GetItem<string>(methodName);
            }

            string languageCode = string.Empty;
            using (var con = DB.NewSqlConnection())
            {
                con.Open();
                using (var reader = DB.GetRSFormat(con, "SELECT [LanguageCode] FROM SystemCountry with (NOLOCK) WHERE [CountryCode] = {0}", DB.SQuote(countryCode)))
                {
                    if (reader.Read())
                    {
                        languageCode = DB.RSField(reader, "LanguageCode");
                    }
                }
            }

            requestCache.AddItem(methodName, languageCode);
            return languageCode;
        }

        public static List<StateDTO> GetStates(string countryCode)
        {
            string rawMethodName = MethodBase.GetCurrentMethod().Name;
            string methodName = string.Format("{0}_{1}_{2}", rawMethodName, countryCode, EXTRACODE);

            var requestCache = CachingFactory.RequestCachingEngineInstance;

            if (requestCache.Exist(methodName))
            {
                return requestCache.GetItem<List<StateDTO>>(methodName);
            }

            var states = new List<StateDTO>();
            using (var con = DB.NewSqlConnection())
            {
                con.Open();
                using (var reader = DB.GetRSFormat(con, "SELECT DISTINCT StateCode, State FROM SystemPostalCode with (NOLOCK) WHERE IsActive = 1 AND CountryCode = {0} AND StateCode IS NOT NULL AND State IS NOT NULL ORDER BY StateCode ASC", DB.SQuote(countryCode)))
                {
                    while (reader.Read())
                    {
                        string code = DB.RSField(reader, "StateCode");
                        string description = DB.RSField(reader, "State");
                        if (!string.IsNullOrEmpty(code) && code.Length > 0)
                        {
                            states.Add(new StateDTO(code, description));
                        }
                    }
                }
            }

            CachingFactory.RequestCachingEngineInstance.AddItem(methodName, states);
            return states;
        }

        public static string GetCompanyLocale(string currentTheadCulture)
        {
            string methodName = string.Format("{0}_{1}_{2}", MethodBase.GetCurrentMethod().Name, currentTheadCulture, EXTRACODE);
            var requestCache = CachingFactory.RequestCachingEngineInstance;

            if (requestCache.Exist(methodName))
            {
                return requestCache.GetItem<string>(methodName);
            }

            string locale = currentTheadCulture;
            using (var con = DB.NewSqlConnection())
            {
                con.Open();
                using (var reader = DB.GetRSFormat(con, "SELECT sl.ShortString FROM SystemCompanyInformation sci with (NOLOCK) INNER JOIN SystemLanguage sl with (NOLOCK) ON sci.CompanyLanguage = sl.LanguageCode"))
                {
                    if (reader.Read())
                    {
                        locale = DB.RSField(reader, "ShortString");
                    }
                }
            }

            requestCache.AddItem(methodName, locale);
            return locale;
        }

        public static string GetCustomerPricingMethod(string customerCode)
        {
            string methodName = string.Format("{0}_{1}_{2}", MethodBase.GetCurrentMethod().Name, customerCode, EXTRACODE);

            var requestCache = CachingFactory.RequestCachingEngineInstance;
            if (requestCache.Exist(methodName))
            {
                return requestCache.GetItem<string>(methodName);
            }

            string pricingMethod = string.Empty;
            using (var con = DB.NewSqlConnection())
            {
                con.Open();
                using (var reader = DB.GetRSFormat(con, "SELECT PricingMethod FROM Customer with (NOLOCK) WHERE CustomerCode = {0}", DB.SQuote(customerCode)))
                {
                    reader.Read();// must read
                    pricingMethod = DB.RSField(reader, "PricingMethod");
                }
            }

            requestCache.AddItem(methodName, pricingMethod);
            return pricingMethod;

        }

        public static string GetLanguageCodeDescription(string sLocaleSetting) 
        {
            string methodName = string.Format("{0}_{1}_{2}", MethodBase.GetCurrentMethod().Name, sLocaleSetting, EXTRACODE);

            var requestCache = CachingFactory.RequestCachingEngineInstance;
            if (requestCache.Exist(methodName))
            {
                return requestCache.GetItem<string>(methodName);
            }

            string languageCodeDescription = string.Empty;

            using (var ds = DB.GetDS("SELECT * FROM eCommerceWebLocaleView with (NOLOCK)", true, DateTime.Now.AddHours(1)))
            {
                foreach (DataRow row in ds.Tables[0].Rows)
                {
                    if (sLocaleSetting == Localization.CheckLocaleSettingForProperCase(DB.RowField(row, "ShortString")))
                    {
                        languageCodeDescription = DB.RowField(row, "LanguageDescription");
                    }
                }
            }

            requestCache.AddItem(methodName, languageCodeDescription);
            return languageCodeDescription;
        }

        public static DataSet GetLocaleSetting(string localeSetting)
        {
            string methodName = string.Format("{0}_{1}_{2}", MethodBase.GetCurrentMethod().Name, localeSetting, EXTRACODE);

            var requestCache = CachingFactory.RequestCachingEngineInstance;
            if (requestCache.Exist(methodName))
            {
                return requestCache.GetItem<DataSet>(methodName);
            }

            var ds = DB.GetDS(string.Format("SELECT * FROM eCommerceWebLocaleView with (NOLOCK) WHERE ShortString = '{0}'", localeSetting), false);
            requestCache.AddItem(methodName, ds);

            return ds;
        }

    }
}
